//////////////////////////////////////////////////////////////////////
//
//  Class Name: MySQLInfo
//
//  Description: This Class defines the actions to connect to, 
//               retrieve and store information from a MySQL Database.
//
//  Revision History
//  Christian Romvary  01JUL2013
//
//////////////////////////////////////////////////////////////////////

package edu.psu.collegerecommendationhelper.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import edu.psu.collegerecommendationhelper.database.GenericDatabase;
import edu.psu.collegerecommendationhelper.model.AccountInfo;
import edu.psu.collegerecommendationhelper.model.CollegeResult;
import edu.psu.collegerecommendationhelper.model.DemographicInfo;
import edu.psu.collegerecommendationhelper.model.EducationInfo;
import edu.psu.collegerecommendationhelper.model.PreferenceInfo;
import edu.psu.collegerecommendationhelper.model.UserBean;


public class MySQLDatabase implements GenericDatabase{
/*
   // EC2 Database
   private String dbName = "CDHDatabase";
   private String dbuserName = "cromvary";
   private String dbpassword = "9Kuskup19Kuskup1";
   private String hostname = "cdhw7.crkptu8dcf8x.us-west-2.rds.amazonaws.com";
   private String port = "3306";
   private String acctable = "accountInfo";
   private String demtable = "demographicInfo";
   private String edutable = "educationInfo";
   private String pretable = "preferenceInfo";
   private String restable = "collegeResult";
*/
   // Local Database
   private String dbName = "CDHDatabase";
   private String dbuserName = "cromvary";
   private String dbpassword = "9Kuskup1";
   private String hostname = "localhost";
   private String port = "3306";
   private String acctable = "accountInfo";
   private String demtable = "demographicInfo";
   private String edutable = "educationInfo";
   private String pretable = "preferenceInfo";
   private String restable = "collegeResult";


/////////////////////////////* PUBLIC *///////////////////////////////

//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveUser 
//
//  Description: This function returns the User information from the
//               MySQL database.
//
//  Inputs: AccountInfo - Account Information retrieved from FaceBook
//
//  Outputs: UserBean - User Information from MySQL DB
//
//////////////////////////////////////////////////////////////////////
   public UserBean retrieveUser (AccountInfo fbInfo) throws ClassNotFoundException {
      long userID = 0;
      UserBean user = new UserBean();

      Class.forName("com.mysql.jdbc.Driver");

      userID = retrieveUserId(fbInfo);

      user.setUser_id(userID);
      user.setAccountInfo(retrieveAccountInfo(userID));
      user.setDemographicInfo(retrieveDemographicInfo(userID));
      user.setEducationInfo(retrieveEducationInfo(userID));
      user.setPreferenceInfo(retrievePreferenceInfo(userID));
      user.setResultList(retrieveCollegeList(userID));

      return(user);

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: saveDemographicInfo 
//
//  Description: This function saves the DemographicInfo into the 
//               MySQL database.
//
//  Inputs: Long - User Identification
//          DemographicInfo - Demographic Information for User
//
//  Outputs: None
//
//////////////////////////////////////////////////////////////////////
   public void saveDemographicInfo (long user_id, DemographicInfo demInfo) {
      // Set sql query
      String sql = "UPDATE " + dbName + "." + demtable +
                   " SET state='" + demInfo.getState() + 
                   "', age='" + demInfo.getAge() +
                   "', sex='" + demInfo.getSex() +
                   "', race='" + demInfo.getRace() +
                   "', relative1='" + demInfo.getRelative1() +
                   "', college1='" + demInfo.getCollege1() +
                   "', relative2='" + demInfo.getRelative2() +
                   "', college2='" + demInfo.getCollege2() +
                   "', relative3='" + demInfo.getRelative3() +
                   "', college3='" + demInfo.getCollege3() +
                   "', relative4='" + demInfo.getRelative4() +
                   "', college4='" + demInfo.getCollege4() +
                   "', relative5='" + demInfo.getRelative5() +
                   "', college5='" + demInfo.getCollege5() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;

      //Attempt to connect to Database
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: saveEducationInfo 
//
//  Description: This function saves the EducationInfo into the MySQL
//               database.
//
//  Inputs: Long - User Identification
//          EducationInfo - Education Information for User
//
//  Outputs: None
//
//////////////////////////////////////////////////////////////////////
   public void saveEducationInfo (long user_id, EducationInfo eduInfo) {
      // Set sql query
      String sql = "UPDATE " + dbName + "." + edutable +
                   " SET GPA='" + eduInfo.getGPA() + 
                   "', mathSAT='" + eduInfo.getMathSAT() +
                   "', creadSAT='" + eduInfo.getCreadSAT() +
                   "', writeSAT='" + eduInfo.getWriteSAT() +
                   "', mathACT='" + eduInfo.getMathACT() +
                   "', readACT='" + eduInfo.getReadACT() +
                   "', englishACT='" + eduInfo.getEnglishACT() +
                   "', scienceACT='" + eduInfo.getScienceACT() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;

      //Attempt to connect to Database
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }

//////////////////////////////////////////////////////////////////////
//
//  Function Name: savePreferenceInfo 
//
//  Description: This function saves the PreferenceInfo into the 
//               MySQL database.
//
//  Inputs: Long - User Identification
//          PreferenceInfo - Preference Information for User
//
//  Outputs: None
//
//////////////////////////////////////////////////////////////////////
   public void savePreferenceInfo (long user_id, PreferenceInfo preInfo) {
      // Set sql query
      String sql = "UPDATE " + dbName + "." + pretable +
                   " SET location='" + preInfo.getLocation() + 
                   "', pubpri='" + preInfo.getPubpri() +
                   "', size='" + preInfo.getSize() +
                   "', sexratio='" + preInfo.getSexratio() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;

      //Attempt to connect to Database
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: saveResultList
//
//  Description: This function saves the CollegeResult into
//               the MySQL Database.
//
//  Inputs: Long - User Identification
//          CollegeResult - College List for User
//
//  Outputs: None
//
//////////////////////////////////////////////////////////////////////
   public void saveResultList (long user_id, CollegeResult resultList) {
      // Set sql query
      String sql = "UPDATE " + dbName + "." + restable +
                   " SET college1='" + resultList.getCollege1() +
                   "', result1='" + resultList.getResult1() +
                   "', college2='" + resultList.getCollege2() +
                   "', result2='" + resultList.getResult2() +
                   "', college3='" + resultList.getCollege3() +
                   "', result3='" + resultList.getResult3() +
                   "', college4='" + resultList.getCollege4() +
                   "', result4='" + resultList.getResult4() +
                   "', college5='" + resultList.getCollege5() +
                   "', result5='" + resultList.getResult5() +
                   "' WHERE user_id=" + user_id + ";";

      PreparedStatement pst = null;

      //Attempt to connect to Database
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }


////////////////////////////* PRIVATE *///////////////////////////////

//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveUserId
//
//  Description: This function retrieves the User ID from the MySQL
//               database using a FaceBook ID.  If no User ID is found,
//               it will initiate a create a new User record.
//
//  Inputs: AccountInfo - Account Information retrieved from FaceBook
//
//  Outputs: long - User ID
//
//////////////////////////////////////////////////////////////////////
   private long retrieveUserId(AccountInfo fbInfo) {
      long user_id = 0;
      String sql = "SELECT * FROM " + dbName + "." + acctable + 
                   " WHERE  FBID = '" + fbInfo.getFbID() + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         if (false == result.next()) {
            user_id = createNewUser(fbInfo);

         }
         else {
            user_id = result.getLong(1);

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return user_id;
   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: createNewUser
//
//  Description: This function creates a new User record in MySQL
//               database.
//
//  Inputs: AccountInfo - Account Information retrieved from FaceBook
//
//  Outputs: long - User ID
//
//////////////////////////////////////////////////////////////////////
   public long createNewUser(AccountInfo fbInfo) {
      long user_id = 0;

      String sql = "INSERT INTO " + dbName + "." + acctable + 
                   " (fbid, firstname, lastname, email) VALUES ('" + 
                   fbInfo.getFbID() + "', '" + fbInfo.getFirstname() + "', '" +
                   fbInfo.getLastname() + "', '" + fbInfo.getEmail() + "');";
      createEntry(sql);

      sql = "INSERT INTO " + dbName + "." + demtable + 
            " (state) VALUES ('SELECT STATE')";
      createEntry(sql);

      sql = "INSERT INTO " + dbName + "." + edutable + 
            " (GPA) VALUES ('0.0')";
      createEntry(sql);

      sql = "INSERT INTO " + dbName + "." + pretable + 
            " (location) VALUES ('SELECT PREFERENCE')";
      createEntry(sql);

      sql = "INSERT INTO " + dbName + "." + restable + 
            " (college1) VALUES ('')";
      createEntry(sql);

      //Need to find way to retrieve user_id
      user_id = retrieveCreatedUserId(fbInfo);

      return user_id;
   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: createEntry
//
//  Description: This function creates a new entry in the MySQL
//               database.
//
//  Inputs: String - SQL command
//
//  Outputs: None
//
//////////////////////////////////////////////////////////////////////
   private void createEntry(String sql) {
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeUpdate();

         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveCreatedUserId
//
//  Description: This function retrieves the User ID of the created
//               User Entry.  Needs to be used instead of
//               retrieveUserID due to MySQL commands.
//
//  Inputs: AccountInfo - Account Information retrieved from FaceBook
//
//  Outputs: long - User ID
//
//////////////////////////////////////////////////////////////////////
   private long retrieveCreatedUserId(AccountInfo fbInfo) {
      long user_id = 0;
      String sql = "SELECT * FROM " + dbName + "." + acctable + 
                   " WHERE  FBID = '" + fbInfo.getFbID() + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         if (false == result.next()) {
            user_id = 0;

         }
         else {
            user_id = result.getLong(1);

         }

         result.close();
         pst.close();
         con.close();

         } catch (SQLException e) {
         e.printStackTrace();
      }

      return user_id;
   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveAccountInfo
//
//  Description: This function retrieves the Account Info from the
//               MySQL database using the UserId.
//
//  Inputs: long - User ID
//
//  Outputs: AccountInfo - Account Information
//
//////////////////////////////////////////////////////////////////////
   private AccountInfo retrieveAccountInfo(long user_id) {
      AccountInfo accountInfo= new AccountInfo();

      String sql = "SELECT * FROM " + dbName + "." + acctable + 
                   " WHERE  USER_ID = '" + user_id + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            accountInfo.setFbID(result.getString(2));
            accountInfo.setFirstname(result.getString(3));
            accountInfo.setLastname(result.getString(4));
            accountInfo.setEmail(result.getString(5));

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return accountInfo;
   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveDemographicInfo
//
//  Description: This function retrieves the Demographic Info from
//               the MySQL database using the UserId.
//
//  Inputs: long - User ID
//
//  Outputs: DemographicInfo - Demographic Information
//
//////////////////////////////////////////////////////////////////////
   public DemographicInfo retrieveDemographicInfo(long user_id) {
      DemographicInfo demographicInfo = new DemographicInfo();

      String sql = "SELECT * FROM " + dbName + "." + demtable + 
                   " WHERE  USER_ID = '" + user_id + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            demographicInfo.setState(result.getString(2));
            demographicInfo.setAge(result.getByte(3));
            demographicInfo.setSex(result.getString(4));
            demographicInfo.setRace(result.getString(5));
            demographicInfo.setRelative1(result.getString(6));
            demographicInfo.setCollege1(result.getString(7));
            demographicInfo.setRelative2(result.getString(8));
            demographicInfo.setCollege2(result.getString(9));
            demographicInfo.setRelative3(result.getString(10));
            demographicInfo.setCollege3(result.getString(11));
            demographicInfo.setRelative4(result.getString(12));
            demographicInfo.setCollege4(result.getString(13));
            demographicInfo.setRelative5(result.getString(14));
            demographicInfo.setCollege5(result.getString(15));

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return demographicInfo;

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveEducationInfo
//
//  Description: This function retrieves the Education Info from
//               the MySQL database using the UserId.
//
//  Inputs: long - User ID
//
//  Outputs: EducationInfo - Education Information
//
//////////////////////////////////////////////////////////////////////
   public EducationInfo retrieveEducationInfo(long user_id) {
      EducationInfo educationInfo = new EducationInfo();

      String sql = "SELECT * FROM " + dbName + "." + edutable + 
                   " WHERE  USER_ID = '" + user_id + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            educationInfo.setGPA(result.getFloat(2));
            educationInfo.setMathSAT(result.getShort(3));
            educationInfo.setCreadSAT(result.getShort(4));
            educationInfo.setWriteSAT(result.getShort(5));
            educationInfo.setMathACT(result.getByte(6));
            educationInfo.setReadACT(result.getByte(7));
            educationInfo.setEnglishACT(result.getByte(8));
            educationInfo.setScienceACT(result.getByte(9));

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return educationInfo;

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrievePreferenceInfo
//
//  Description: This function retrieves the Preference Info from
//               the MySQL database using the UserId.
//
//  Inputs: long - User ID
//
//  Outputs: PreferenceInfo - Preference Information
//
//////////////////////////////////////////////////////////////////////
   public PreferenceInfo retrievePreferenceInfo(long user_id) {
      PreferenceInfo preferenceInfo = new PreferenceInfo();

      String sql = "SELECT * FROM " + dbName + "." + pretable + 
            " WHERE  USER_ID = '" + user_id + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
            preferenceInfo.setLocation(result.getString(2));
            preferenceInfo.setPubpri(result.getString(3));
            preferenceInfo.setSize(result.getString(4));
            preferenceInfo.setSexratio(result.getString(5));

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return preferenceInfo;

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: retrieveCollegeList
//
//  Description: This function retrieves the College List from the
//               MySQL database using the UserId.
//
//  Inputs: long - User ID
//
//  Outputs: CollegeResult - List of College Results
//
//////////////////////////////////////////////////////////////////////
   public CollegeResult retrieveCollegeList(long user_id) {
      CollegeResult resultList = new CollegeResult();

      String sql = "SELECT * FROM " + dbName + "." + restable + 
                   " WHERE  USER_ID = '" + user_id + "';";

      ResultSet result = null;
      PreparedStatement pst = null;
      Connection con = getConnection(getDBUrl());

      try {
         pst = con.prepareStatement(sql);
         pst.executeQuery();
         result = pst.getResultSet();

         while (result.next()) {
        	resultList.setCollege1(result.getString(2));
        	resultList.setResult1(result.getString(3));
            resultList.setCollege2(result.getString(4));
            resultList.setResult2(result.getString(5));
            resultList.setCollege3(result.getString(6));
            resultList.setResult3(result.getString(7));
            resultList.setCollege4(result.getString(8));
            resultList.setResult4(result.getString(9));
            resultList.setCollege5(result.getString(10));
            resultList.setResult5(result.getString(11));

         }

         result.close();
         pst.close();
         con.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }

      return resultList;

   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: getConnection
//
//  Description: This function attempts to initialize a connection
//               with a MySQL Database.
//
//  Inputs: String - Database URL
//
//  Outputs: Connection - Connection to the Database
//
//////////////////////////////////////////////////////////////////////
   private Connection getConnection(String DB_URL) {
      Connection con = null;

      try {
         con = DriverManager.getConnection(DB_URL);
      } catch (SQLException ex) {
         System.out.println(ex.getMessage());
      }
      finally { }

      return con;
   }


//////////////////////////////////////////////////////////////////////
//
//  Function Name: getDBUrl
//
//  Description: This function returns the URL needed to connect to
//                the MySQL Database.
//
//Inputs: None
//
//Outputs: String - Driver Class Path
//
//////////////////////////////////////////////////////////////////////
   private String getDBUrl()
   {
      String DB_URL = "jdbc:mysql://" + this.hostname + ":" + this.port +
                      "/" + this.dbName + "?user=" + this.dbuserName +
                      "&password=" + this.dbpassword;

      return DB_URL;  
   }

	
}
